/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import com.sun.xml.internal.ws.org.objectweb.asm.Type;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.*;
import pojo.BanTinPoJo;
import pojo.DanhMucPoJo;
import util.DataProvider;

/**
 *
 * @author Kanet
 */
public class BanTinDao {
    
    // <editor-fold defaultstate="collapsed" desc="1. Chung">
    private static ArrayList<BanTinPoJo> LayDanhSach(String strSql) throws ParseException{
        ArrayList<BanTinPoJo> danhsach =null;
        DataProvider dp=new DataProvider();
        dp.open();
        try {
            ResultSet rs = dp.executeQuery(strSql);
            danhsach =new ArrayList<BanTinPoJo>();
            BanTinPoJo sp=null;
            while (rs.next()) {
                sp=new BanTinPoJo();
                sp.setidBanTin(rs.getInt("idBanTin"));
                sp.setnoiDung(rs.getString("noiDung"));
                sp.settieuDe(rs.getString("tieuDe"));
                sp.setngayDang(rs.getDate("ngayDang"));
                sp.setngayHetHan(rs.getDate("ngayHetHan"));
                sp.setLuotXem(rs.getInt("luotXem"));
                sp.setLienKet(rs.getString("lienKet"));
                sp.setHinhAnh(rs.getString("hinhAnh"));
                sp.setGiamGia(rs.getInt("giamGia"));
                sp.setGiaBan(rs.getInt("giaBan"));
                sp.setGiaGoc(rs.getInt("giaGoc"));
                sp.setDiaChi(rs.getString("diaChi"));
                sp.getKhuVuc().setTenKhuVuc("tenKhuVuc");
                sp.getDanhMuc().setTenDanhMuc(rs.getString("tenDanhMuc"));
                sp.getLoaiTin().setTenLoaiTin(rs.getString("tenLoaiTin"));
                sp.getTaiKhoan().setIdTaiKhoan(rs.getInt("idTaiKhoan"));
                sp.getTinhTrang().setTenTinhTrang(rs.getString("tenTinhTrang"));
                danhsach.add(sp);
            }
            dp.close();
        } catch (SQLException ex) {
            Logger.getLogger(BanTinDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return danhsach;
    }
    private static int LayDong(String strSql){
        int soDong=0;
        DataProvider dp=new DataProvider();
        dp.open();
        try{
            ResultSet rs = dp.executeQuery(strSql);
            while(rs.next())
                soDong++;
            dp.close();
        } catch (SQLException ex) {
            Logger.getLogger(BanTinDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return soDong;
    }
    private static BanTinPoJo LayDoiTuong(String strSql) throws ParseException{
        BanTinPoJo sp=null;
        DataProvider dp=new DataProvider();
        dp.open();
        try{
            ResultSet rs = dp.executeQuery(strSql);
            if(rs.next()) {
                sp = new BanTinPoJo();
                
                sp.setidBanTin(rs.getInt("idBanTin"));
                sp.setnoiDung(rs.getString("noiDung"));
                sp.settieuDe(rs.getString("tieuDe"));
                sp.setngayDang(rs.getDate("ngayDang"));
                sp.setngayHetHan(rs.getDate("ngayHetHan"));
                sp.setLuotXem(rs.getInt("luotXem"));
                sp.setLienKet(rs.getString("lienKet"));
                sp.setHinhAnh(rs.getString("hinhAnh"));
                sp.setGiamGia(rs.getInt("giamGia"));
                sp.setGiaBan(rs.getInt("giaBan"));
                sp.setGiaGoc(rs.getInt("giaGoc"));
                sp.setDiaChi(rs.getString("diaChi"));
                sp.getKhuVuc().setTenKhuVuc("tenKhuVuc");
                sp.getDanhMuc().setTenDanhMuc(rs.getString("tenDanhMuc"));
                sp.getLoaiTin().setTenLoaiTin(rs.getString("tenLoaiTin"));
                sp.getTaiKhoan().setIdTaiKhoan(rs.getInt("idTaiKhoan"));              

            }
            dp.close();
        } catch (SQLException ex) {
            Logger.getLogger(BanTinDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sp;
    }
// </editor-fold>
    
    public static ArrayList<BanTinPoJo> LayDanhSachBanTin() throws ParseException
    {
        String sql ="Select * from bantin";
        return LayDanhSach(sql);
    }
    public static ArrayList<BanTinPoJo> LayDanhSachTheoKhuVuc(int idKhuVuc) throws ParseException
    {
        String sql ="Select * from bantin b, khuvuc kv where kv.idKhuVuc=b.idKhuVuc and kv.idKhuVuc= " +idKhuVuc;
        return LayDanhSach(sql);
    }
    public static ArrayList<BanTinPoJo> LayDanhSachTheoDichVu(int idDichVu) throws ParseException{
        String sql="Select * from bantin bt,danhmuc dm where bt.iddanhmuc=dm.iddanhmuc and dm.iddichvu="+idDichVu;
        return LayDanhSach(sql);
    }
    public static ArrayList<BanTinPoJo> LayDanhSachTheoDanhMuc(int idDanhMuc) throws ParseException
    {
        String sql="Select * from bantin bt,danhmuc dm where bt.iddanhmuc="+idDanhMuc;
        return LayDanhSach(sql);
    }
    
    public int ThemBanTin(BanTinPoJo bt)
    {
        DataProvider dp = new DataProvider();
        int id = 0;
        
        try{
            dp.open();
            
            String sql = "{ call sp_ThemBanTin(?,?,?,?,?,?,?,?,?,?,?,?,?) }";
            CallableStatement stm = dp.prepareCall(sql);
            stm.setString(1, bt.gettieuDe());
            stm.setString(2, bt.getnoiDung());
            stm.setString(3, bt.getHinhAnh());
            stm.setDate(4, new java.sql.Date(bt.getngayDang().getTime()));
            stm.setDate(5, new java.sql.Date(bt.getngayHetHan().getTime()));
            stm.setInt(6, bt.getDanhMuc().getIdDanhMuc());
            stm.setInt(7, bt.getLoaiTin().getidLoaiTin());
            //stm.setInt(8, bt.getTaiKhoan().getIdTaiKhoan());
            stm.setInt(8, 23);
            stm.setInt(9, bt.getKhuVuc().getIdKhuVuc());
            stm.setInt(10, 2);
            //stm.setInt(10, bt.getTinhTrang().getIdTinhTrang());
            stm.setInt(11, bt.getGiaBan());
            stm.setInt(12, bt.getGiaGoc());
            //stm.setInt(13, id);
            stm.registerOutParameter(13, Type.INT);
            
            stm.executeUpdate();
            
            id = stm.getInt(13);
            
            System.out.println(" ID: " + id);
            bt.setidBanTin(id);
            
        }catch(Exception ex)
        {
            ex.printStackTrace();
            return 0;
        }
        finally
        {
            dp.close();
        }
        return id;
    }
    
    public boolean CapNhatBanTin(BanTinPoJo bt)
    {
        DataProvider dp = new DataProvider();
        try{
            dp.open();
            String sql = "{ call sp_CapNhatBanTin(?,?,?,?,?,?,?,?,?,?,?,?,?) }";
            CallableStatement stm = dp.prepareCall(sql);
            stm.setInt(1, bt.getidBanTin());
            stm.setString(2, bt.gettieuDe());
            stm.setString(3, bt.getnoiDung());
            stm.setString(4, bt.getHinhAnh());
            stm.setDate(5, new java.sql.Date(bt.getngayDang().getTime()));
            stm.setDate(6, new java.sql.Date(bt.getngayHetHan().getTime()));
            stm.setInt(7, bt.getDanhMuc().getIdDanhMuc());
            stm.setInt(8, bt.getLoaiTin().getidLoaiTin());
            //stm.setInt(8, bt.getTaiKhoan().getIdTaiKhoan());
            stm.setInt(9, 23);
            stm.setInt(10, bt.getKhuVuc().getIdKhuVuc());
            stm.setInt(11, 2);
            //stm.setInt(10, bt.getTinhTrang().getIdTinhTrang());
            stm.setInt(12, bt.getGiaBan());
            stm.setInt(13, bt.getGiaGoc());
            stm.executeUpdate();
            
        }catch(Exception ex)
        {
            ex.printStackTrace();
            return false;
        }
        finally
        {
            dp.close();
        }
         
        return true;
    }
    
    
    
    public List<BanTinPoJo> TimBanTin(int loaiTin, int idDM, int idDV, int hethan)
    {
        List<BanTinPoJo> ds = new ArrayList<BanTinPoJo>();
        DataProvider dp = new DataProvider();
        try{
            dp.open();
            CallableStatement stm = dp.prepareCall("{CALL sp_TimBanTin(?,?,?,?)}");
            stm.setInt(1, loaiTin);
            stm.setInt(2, idDM);
            stm.setInt(3, idDV);
            stm.setInt(4, hethan);
            ResultSet rs= stm.executeQuery();
            BanTinPoJo tk;
            while(rs.next())
            {
                tk = new BanTinPoJo();
                tk.setidBanTin(rs.getInt("idBanTin"));
                tk.setDanhMuc(new DanhMucPoJo(rs.getInt("idDanhMuc")));
                tk.settieuDe(rs.getString("tieuDe"));
                tk.setnoiDung(rs.getString("noiDung"));
                tk.setngayHetHan(rs.getDate("ngayHetHan"));
                tk.setHinhAnh(rs.getString("hinhAnh"));
                tk.setGiaBan(rs.getInt("giaBan"));
                ds.add(tk);
            }
            rs.close();
            
		stm.close();
			
            }catch(Exception ex)
            {
		ex.printStackTrace();
            }
            finally
            {
                dp.close();
            }
            return ds;
    }
    
    public List<BanTinPoJo> TimCoupon(int hethan)
    {
        List<BanTinPoJo> ds = new ArrayList<BanTinPoJo>();
        DataProvider dp = new DataProvider();
        try{
            dp.open();
            CallableStatement stm = dp.prepareCall("{CALL sp_LayCoupon(?)}");
            stm.setInt(1, hethan);
            ResultSet rs= stm.executeQuery();
            BanTinPoJo tk;
            while(rs.next())
            {
                tk = new BanTinPoJo();
                tk.setidBanTin(rs.getInt("idBanTin"));
                tk.setDanhMuc(new DanhMucPoJo(rs.getInt("idDanhMuc")));
                tk.settieuDe(rs.getString("tieuDe"));
                tk.setnoiDung(rs.getString("noiDung"));
                tk.setngayHetHan(rs.getDate("ngayHetHan"));
                tk.setHinhAnh(rs.getString("hinhAnh"));
                tk.setGiaBan(rs.getInt("giaBan"));
                tk.setGiamGia(rs.getInt("giamGia"));
                ds.add(tk);
            }
            rs.close();
            
		stm.close();
			
            }catch(Exception ex)
            {
		ex.printStackTrace();
            }
            finally
            {
                dp.close();
            }
            return ds;
    }
    
}
